/*
 * Created on Feb 19, 2004
 *
 * Author: Sandeep Prakash
 * Project: Sucxentv2 (using leaf nodes only)
 */
package dbsucxentW.storageEdgeW.loadersW.relationalW;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;

import dbsucxentW.constantW.Constant;
import dbsucxentW.constantW.EdgeConstant;
import dbsucxentW.storageEdgeW.entitiesW.LeafNode;
import dbsucxentW.storageEdgeW.entitiesW.PathCollection;
import dbsucxentW.storageEdgeW.loadersW.Store;
//klarinda 20060901 DocumentSummary not used in Edge
//import dbsucxent.storageEdge.parser.DocumentSummary;

/**
 * @author Administrator
 *
 * 
 */
public class RelationalStore extends Store {
	
	private Connection _dbConnection;
	private Statement _stmt;
	
	private JdbcOptionsProvider _urlProvider;
	//k 20060525 private int _docId = -1;
	
	/*klarinda 20060905 use bulk loading 
	private StringBuilder _sql;
	private long _t3 = 0;
	private int _counter = 0;
	private int _progress = 0;
	private int _totalCounter = 0;
	*/

	long t1;
	
//	//bulk loader (klarinda 20060905)
//	protected final static int NO_OF_TABLES 	= 4;	//attribute, doc, edge path
//	protected final static int DOCUMENT_TABLE 	= 0;	
//	protected final static int PATH_TABLE 		= 1;	
//	protected final static int EDGE_TABLE 		= 2;	
//	protected final static int ATTRIBUTE_TABLE 	= 3;	
//	//protected final static String BULKFILEPATH = "D:/sucxent/sucxent/bulk/Edge/";
//	protected final static String BULKFILEPATH = "C:/Klarinda/sucxent/bulk/Edge/"; //for experiments
//	protected final static String[] TABLE_NAMES = {"Document", "Path", "Edge", "Attribute"};
	
	
	protected BufferedWriter _writers[];
	private String _dbName;
	//end of bulk loader
	
	/**
	 * Constructor. Set JdbcURLProvider as URL format etc.. differ
	 * and so do some syntax aspects.
	 */
	//klarinda 20060905 add _dbName, 20070525 add file
	//public RelationalStore( JdbcOptionsProvider urlProvider, Connection dbConnection) {
	public RelationalStore( JdbcOptionsProvider urlProvider, Connection dbConnection, String dbName, String file) {
		super();
		_urlProvider = urlProvider;
		_dbConnection = dbConnection;
		//k 20070525 what's _first used for? _first = 0;
		_dbName = dbName;		//klarinda 20060905
		_file = file;			//klarinda 20070525
		//
		//startBatch() is needed as different DBs have different way of 
		//specifying start of a batch statement.
		//klarinda 20060905 use bulk loading _sql = new StringBuilder( _urlProvider.startBatch() );
		createFiles();//klarinda 20060905 bulk loader	
		
		//klarinda 20070525 generateDocId:
		try{
			generateDocId();
		}
		catch(Exception e){
			System.out.println("RelationalStore: error at generating docId!");
			System.out.println(e.getMessage());
		}
	}

	
	/**
	 * Generate the document Id. Add file name to database and get the auto increment
	 * value.
	 */
	public void generateDocId() throws Exception {
		System.out.println("RelationalStore.generateDocId()");
		_stmt = _dbConnection.createStatement();
		ResultSet rs = _stmt.executeQuery( "SELECT MAX(docid) FROM Document" );
		
		if( rs.next() ){
			_docId = rs.getInt( 1 ) + 1;
		} else {
			_docId = 0;
		}
		
		rs.close();
		_stmt.close();
		
		try{	//write each row to file for bulk load
			_writers[EdgeConstant.DOCUMENT_TABLE].append(_docId + "\t" + _file );
			_writers[EdgeConstant.DOCUMENT_TABLE].newLine();
		} catch (IOException e) {
			System.out.println("ERR at bulk load generateDocId");
			System.out.println(e.getMessage());
		}
	}
	

	public void init() throws Exception{
		//klarinda 20070525 no need loadJDBC - loadJDBC();

		//
		//Store DocumentSummary
		//
		t1 = System.currentTimeMillis();
		
		/*DocumentSummary summary = _collection.getSummary();
		int maxDepth = summary.getMaxDepth();
		
		for( int i = 0; i < maxDepth; i++ ){
			_sql.append( "INSERT INTO DocumentRValue VALUES( " );
			_sql.append( _docId );
			_sql.append( ", " );
			_sql.append( i+1 );
			_sql.append( ", " );
			_sql.append( (summary.getRValue( i+1 ) - 1) / 2 + 1 );
			_sql.append( " );" );
		}*/
	}

	/*klarinda 20060901 is this function ever used? 
	private void storePaths() {
		//
		//Store paths
		//
		PathCollection paths = _collection.getPathCollection();
		
		while( paths.hasMorePaths() ){
			
			String path = paths.newPath();
			
			if( path == null ){
				//Launcher.getLoggerInstance().info("oldman");
				break;
			}
			
			// count path len
			int x, y;
			y=0;
			for (x=0;x < path.length(); x++){
				if (path.charAt(x) == '.'){
					y++;
				}
			}
			
			int pathId = paths.getPathId( path, this );

			_sql.append( "INSERT INTO Path VALUES(" );
			_sql.append( pathId );
			_sql.append( ", '" );
			_sql.append( path );
			_sql.append( "');\n" );
		}
		
		//System.out.println( _sql.toString() );
	}
	*/

	public void storePath(int pathId, String path) {
		/*klarinda 20060905 bulk loader
		_sql.append( "INSERT INTO Path VALUES(" );
		_sql.append( pathId );
		_sql.append( ", '" );
		_sql.append( path );
		_sql.append( "');\n" );
		*///klarinda 20060905 bulk loader
		try{
			_writers[EdgeConstant.PATH_TABLE].append(pathId + "\t" + path);
			_writers[EdgeConstant.PATH_TABLE].newLine();
		} catch (IOException e) {
			System.out.println("ERR at bulk load Path");
			System.out.println(e.getMessage());
		}
		//end of klarinda 
	}
	
	/* klarinda 20060905 use bulk loading 
	public void add(LeafNode node) throws Exception{
		
		//klarinda 20060828 add if isAttribute
		if ( node.getIsAttribute() ){
			_sql.append( "INSERT INTO Attribute VALUES( " );
			_sql.append( _docId );
			_sql.append( ", " );
			_sql.append( node.getParentId() );
			_sql.append( ", " );
			_sql.append( node.getPathId() );
			_sql.append( ", '" + node.getValue() );
			_sql.append( "' );\n" );
		} else 
		if ( node.getIsLeaf() ){
			_sql.append( "INSERT INTO Edge VALUES( " );
			_sql.append( _docId );
			_sql.append( ", " );
			_sql.append( node.getOrder() );
			_sql.append( ", " );
			_sql.append( node.getParentId() );
			_sql.append( ", " );
			_sql.append( node.getDescendantId() );
			_sql.append( ", " );
			_sql.append( node.getPathId() );
			_sql.append( ", '" + node.getValue() );
			_sql.append( "' );\n" );
		} else {
			_sql.append( "INSERT INTO Edge VALUES( " );
			_sql.append( _docId );
			_sql.append( ", " );
			_sql.append( node.getOrder() );
			_sql.append( ", " );
			_sql.append( node.getParentId() );
			_sql.append( ", " );
			_sql.append( node.getDescendantId() );
			_sql.append( ", " );
			_sql.append( node.getPathId() );
			_sql.append( ", null" );
			_sql.append( " );\n" );
		
		}
		
		node = null;
		
		_counter++;
		_totalCounter = _docId;
		
		if( _counter > 5000 ){ //klarinda 20060901 initially was == 5000
			//Multi-thread this portion.
			try{
				_sql.append( _urlProvider.endBatch() );
				System.out.println( "RelationalStore.add(), _sql: \n"+_sql);//klarinda 20060901
				
				//Only for timing..remove later.
				long t2 = System.currentTimeMillis();
				
				Launcher.getLoggerInstance().info( "Time taken to build SQL stmt = " + ( t2-t1 ) + "ms." );
				
				_stmt.execute( _sql.toString() );
				
				t1 = System.currentTimeMillis();
				
				Launcher.getLoggerInstance().info( "Time taken for insert = " + ( t1-t2 ) + "ms." );
				Launcher.getLoggerInstance().info( "5000 inserted." );
				_t3 += ( t1-t2 );
				_sql = null;
				_sql = new StringBuilder( _urlProvider.startBatch() );

				_counter = 0;
				_progress++;
				
				if( Launcher.monitor != null )
				{
				    Launcher.monitor.worked( _progress * 5000 );
				}
				
			}catch( Exception ex ){
				ex.printStackTrace();
				System.out.println("SQL: "+_sql);
				Launcher.getLoggerInstance().error( "Error encountered.." + ex.getMessage() );
				
				FileWriter w = new FileWriter( "log.txt" );
				w.write( _sql.toString() );
				w.close();
				
				//System.exit( 0 );
			}
		}

	}
	*/

/*	klarinda 20060905 bulk loader
	public void commit() throws Exception{
	    try
	    {
			//klarinda 20060901 CREATE FOREIGN KEY between Attribute and Edge
	    	_sql.append("ALTER TABLE dbo.Attribute ADD CONSTRAINT \n");
	    	_sql.append("    FK_Attribute_Edge FOREIGN KEY \n");
	    	_sql.append("    (	docid,	parent_id	) \n"); 
   			_sql.append("    REFERENCES dbo.Edge \n"); 
			_sql.append("    (	docid,	id	) \n"); 
			_sql.append("    ON UPDATE  NO ACTION \n"); 
	    	_sql.append("    ON DELETE  NO ACTION \n"); 
			//end of klarinda 
			
	    	System.out.println( "RelationalStore.commit(), _sql: \n"+_sql);	//klarinda 20060822
			//Launcher.getLoggerInstance().info( "Commiting batch now.." );
			
			//storePaths();
			
			if( _sql.toString().length() > 2 )
			{
				//System.out.println( _sql.toString() );
				_stmt.execute( _sql.toString() );
	
			}
			
			if ( (_totalCounter % 50) == 0){
				Launcher.getLoggerInstance().info( "Batch commited.." + _totalCounter );
				_totalCounter = 0;
			}
			//new area
			_sql = null;
			_sql = new StringBuilder( _urlProvider.startBatch() );

			_dbConnection.commit();
			//_stmt.close();
			
			//new area
			//_dbConnection.close();
			
			//klarinda 20060905 bulk loader
			System.out.println("RelationalStore.commit() closing all writers");
			try{
				//for (int x = 0; x < NO_OF_TABLES; x++){
				//	_writers[x].flush();
				//	_writers[x].close();
				//}
				//only create PATH, DOCUMENT, DOCRVALUE TABLE
				_writers[DOCUMENT_TABLE].flush();
				_writers[DOCUMENT_TABLE].close();
				_writers[PATH_TABLE].flush();
				_writers[PATH_TABLE].close();
			} catch (IOException e) {
				System.out.println(e.getMessage());
			}
			//end of klarinda
	    }catch( Exception ex )
	    {
	        FileWriter w = new FileWriter( "log.txt" );
			System.out.println("Error commiting");
			w.write( _sql.toString() );
			w.close();
			
			ex.printStackTrace();
			

			System.exit( 0 );
	    }
	}
*/
	
/*	klarinda 20070525 no need loadJDBC
	protected void loadJDBC() throws Exception{
		//Class.forName( _urlProvider.driverName() );
		//_dbConnection = DriverManager.getConnection( _urlProvider.jdbcURL(), 
		//				_urlProvider.username(), _urlProvider.password() );
		
		//_dbConnection.setAutoCommit( false );
		
		//document id is generated first.
		generateDocId();
		
	}
*/	
	public void complete() throws Exception{
		//storePaths();
		/* klarinda 20060905 bulk loader
    	System.out.println( "RelationalStore.complete(), _sql: "+_sql);		//klarinda 20060822
		
		if (_sql.toString().equals("")){
		}else{
		_stmt.execute( _sql.toString() );
		_dbConnection.commit();
		}
		
		//if (_docId == 1)
		//Launcher.getLoggerInstance().info( "SQL TIME.." + _t3 );
		
		_stmt.close();
		
		_stmt = null;
		*/
	}
	
	//klarinda 20060905 bulk loading
	private void createFiles(){
		String fname;
		_writers = new BufferedWriter[EdgeConstant.NO_OF_TABLES];
		System.out.println("RelationalStore.createFiles()");
		//only create DOC, PATH file
		try{
			// add by erwin 2007-07-04
			File dir1 = new File(".");
			EdgeConstant.BULKFILEPATH = dir1.getCanonicalPath() +"\\bulk\\";
			// end
			fname = EdgeConstant.BULKFILEPATH + EdgeConstant.TABLE_NAMES[EdgeConstant.DOCUMENT_TABLE] + ".tbl";
			System.out.println("RelationalStore.createFiles(): "+fname);
			_writers[EdgeConstant.DOCUMENT_TABLE] = new BufferedWriter(new FileWriter(fname));

			fname = EdgeConstant.BULKFILEPATH + EdgeConstant.TABLE_NAMES[EdgeConstant.PATH_TABLE] + ".tbl";
			System.out.println("RelationalStore.createFiles(): "+fname);
			_writers[EdgeConstant.PATH_TABLE] = new BufferedWriter(new FileWriter(fname));
		} catch (IOException e) {
			System.out.println(e.getMessage());
		}

		System.out.println("RelationalStore.createFiles() success");
	}	

	//klarinda 20060905 load tables into db
	//public void loadDb(){
	public void commit() throws Exception{
		//k20070525 add TextContent & AttrContent
		String fname, sql;
		String db = "\"" + _dbName + "\"";

		System.out.println("RelationalStore.commit() closing all writers");
		try{
			//for (int x = 0; x < NO_OF_TABLES; x++){
			//	_writers[x].flush();
			//	_writers[x].close();
			//}
			//only create PATH, DOCUMENT, DOCRVALUE TABLE
			_writers[EdgeConstant.DOCUMENT_TABLE].flush();
			_writers[EdgeConstant.DOCUMENT_TABLE].close();
			_writers[EdgeConstant.PATH_TABLE].flush();
			_writers[EdgeConstant.PATH_TABLE].close();
		} catch (IOException e) {
			System.out.println(e.getMessage());
		}
		
		for (int x = 0; x < EdgeConstant.NO_OF_TABLES; x++){
		
			try{
				fname = EdgeConstant.BULKFILEPATH + EdgeConstant.TABLE_NAMES[x] + ".tbl";
				
				sql = "USE master;\n";
				sql += "ALTER DATABASE " + db + " SET RECOVERY BULK_LOGGED \n";
				sql += "USE " + db + ";\n";
				sql += "BULK INSERT " + EdgeConstant.TABLE_NAMES[x] + " \n";
				sql += "FROM '" + fname + "'\n";
				
				if(x==EdgeConstant.DOCUMENT_TABLE)
					sql += "WITH (ORDER(DocId), TABLOCK);\n";
				else if (x==EdgeConstant.PATH_TABLE)
					sql += "WITH (ORDER(Path_Id), TABLOCK);\n";
				else if (x==EdgeConstant.EDGE_TABLE || x==EdgeConstant.TEXTCONTENT_TABLE)
					//20060907 EDGE is not ordered based on (DocId,Id) bcoz post-order
					//sql += "WITH (ORDER(DocId,Id), TABLOCK);\n";
					sql += "WITH (ORDER(DocId), TABLOCK);\n";
				else //ATTRIBUTE
					//20060907 ATTRIBUTE may not be ordered based on Path_Id
					//sql += "WITH (ORDER(DocId,Parent_Id,Path_Id), TABLOCK);\n";
					sql += "WITH (ORDER(DocId,Parent_Id), TABLOCK);\n";
				
				sql += "USE master;\n";
				sql += "ALTER DATABASE " + db + " SET RECOVERY FULL;\n";
				
				System.out.println("--Attempting: " + fname + "\n" + sql);
				
				_stmt = _dbConnection.createStatement();
				_stmt.execute(sql);
				_dbConnection.commit();	
				_stmt.close();
						
			} catch (Exception e) {
				System.out.println(e.getMessage());
			}
		}
		
		sql = "USE " + db + ";\n";
		System.out.println(sql);
		_stmt = _dbConnection.createStatement();
		_stmt.execute(sql);
		_dbConnection.commit();	
		_stmt.close();
	}

	/**
	 * klarinda 20070525 To generate Statistics (PathCount, TotalPathCount, ValueSelectivity)
	 * the statisctis are stored inside the table
	 * Please refer to Sandeep's Prefetching paper for definition of each term 
	 */
	public void generateStatistics() throws Exception {
		String db = "\"" + _dbName + "\"";

    	BufferedReader in = new BufferedReader(new FileReader("script/EDGE_statistics.sql"));;
    	String sqlscript = "USE " + db + ";\n", str;

        while ((str = in.readLine()) != null) {
        	sqlscript = sqlscript + str + "\n";
        }
        in.close();
		System.out.println("Sucxent.generateStatistics() complete script:\n"+sqlscript);
		
		_stmt = _dbConnection.createStatement();
		_stmt.execute(sqlscript);
		_dbConnection.commit();	
		_stmt.close();

	}
	
}
